package com.cx.utils;

import com.cx.entity.PushedData;
import com.cx.entity.ZtCustomer;
import com.cx.service.PushedDataService;
import com.cx.service.ZtCustomerService;
import lombok.Data;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.lang.model.element.NestingKind;
import java.io.*;
import java.util.Date;
import java.util.List;

/**
 * @ClassName: ExcelUtils
 * @Description: Excel工具类
 * @Author: cx
 * @Date: 2024/2/22 16:22
 **/
@Component
public class ExcelUtils {
    @Autowired
    private PushedDataService pushedDataService;

    @Autowired
    private ZtCustomerService ztCustomerService;

    @Value("${src.sdk}")
    private String url;

    /**
     * @return void
     * @Description //学习拉取表格数据
     * @Param No such property: code for class: Script1
     * @Date 2024/2/27 9:51
     * @Author cx
     **/
    public static void makeExcel() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        System.out.println(workbook);

        //创建Excel表格
        Sheet sheet = workbook.createSheet("java Table");

        //在工作表中创建一行，行的索引是0
        Row row = sheet.createRow(0);

        //在这行中创建单元格
        //第一个
        Cell cell = row.createCell(0);
        //赋值
        cell.setCellValue("Column One");

        //第二个
        Cell cell2 = row.createCell(1);
        cell2.setCellValue("Column two");

        //第三个
        Cell cell3 = row.createCell(2);
        cell3.setCellValue("Column three");


        // 这里是创建下拉列表的代码
//
//        firstRow：第一个单元格的行索引
//        lastRow：最后一个单元格的行索引
//        firstCol：第一个单元格的列索引
//        lastCol：最后一个单元格的列索引
        CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, 2, 2);
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(new String[]{"Option 1", "Option 2", "Option 3"});
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setShowErrorBox(true);
        sheet.addValidationData(dataValidation);

        //将工作簿写入到一个文件中 "F:/test/"
        String name = TimeTask.dateToString(new Date()) + "workbook.xlsx";
        FileOutputStream fileOut = new FileOutputStream("F:/test/" + name);

        workbook.write(fileOut);
        fileOut.close();

        // 关闭工作簿
        workbook.close();
    }


    /**
     * @return void
     * @Description //导出pushed_data表格的数据
     * @Param No such property: code for class: Script1
     * @Date 2024/2/26 13:23
     * @Author cx
     **/
    public  void exportPushedExcel() throws IOException {

        PushedData pushedData = new PushedData();
        //制作Excel表格
        Workbook workbook = new XSSFWorkbook();
        //创建Excel表格
        Sheet sheet = workbook.createSheet("push_data");
        //在工作表中创建一行，行的索引是0
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("设备名称");
        row.createCell(2).setCellValue("捕获状态");
        row.createCell(3).setCellValue("人脸质量分数");
        row.createCell(4).setCellValue("抓拍时间");
        row.createCell(5).setCellValue("人脸路径");
        row.createCell(6).setCellValue("场景图");
        row.createCell(7).setCellValue("名称");
        row.createCell(8).setCellValue("人像库");
        row.createCell(9).setCellValue("人像库图片url");
        row.createCell(10).setCellValue("备注");
        PushedData pushedData1 = pushedDataService.selectByPrimaryKey((long) 9);
        System.out.println(pushedData1);
        List<PushedData> all = pushedDataService.getAll();
        for (int i = 0; i < all.size(); i++) {
            Row rows = sheet.createRow(i + 1);
            rows.createCell(0).setCellValue(all.get(i).getId());
            rows.createCell(1).setCellValue(all.get(i).getChannelName());
            rows.createCell(2).setCellValue(all.get(i).getTrackStatus());
            rows.createCell(3).setCellValue(all.get(i).getFaceQuality());
            rows.createCell(4).setCellValue(all.get(i).getIso8601timestamp());
            rows.createCell(5).setCellValue(all.get(i).getFaceImage());
            rows.createCell(6).setCellValue(all.get(i).getSceneImage());
            rows.createCell(7).setCellValue(all.get(i).getReserved());
            rows.createCell(8).setCellValue(all.get(i).getReserved2());
            rows.createCell(9).setCellValue(all.get(i).getReserved3());
            rows.createCell(10).setCellValue(all.get(i).getReserved4());

        }


        //将工作簿写入到一个文件中 "F:/test/"
        String name = TimeTask.dateToString(new Date()) + "workbook.xlsx";
        FileOutputStream fileOut = new FileOutputStream("F:/test/" + name);

        workbook.write(fileOut);
        fileOut.close();

        // 关闭工作簿
        workbook.close();

    }


    /**
     * @return void
     * @Description //根据客户Excel表导入数据库
     * @Param No such property: code for class: Script1
     * @Date 2024/2/27 9:53
     * @Author cx
     **/
    public void importData() throws IOException {
        Workbook workbook = WorkbookFactory.create(new File("F:/test/customer.xlsx"));
        //获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        int emptyRowCount = 0;
        for (int i = 39; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            //连续遇到两个空行终止循环
            if (row == null || row.getCell(0) == null || "".equals(row.getCell(0).getStringCellValue().trim())) {
                emptyRowCount++;
                if (emptyRowCount >= 2) {
                    break;
                }
                continue;
            } else {
                emptyRowCount = 0;
            }
            ZtCustomer ztCustomer = new ZtCustomer();
            ztCustomer.setCustomerName(row.getCell(0).getStringCellValue());
            ztCustomer.setCustomerAbbreviation(row.getCell(1).getStringCellValue());

            ztCustomer.setCountriseRegions(row.getCell(2).getStringCellValue());
            ztCustomer.setEnterpriseWebsite(row.getCell(3).getStringCellValue());
            //设置主营产品
            ztCustomer.setMeritoProduct(row.getCell(4).getStringCellValue());


            ztCustomer.setCustomeType(row.getCell(5).getStringCellValue());


            ztCustomer.setRemark(row.getCell(6).getStringCellValue());
            ztCustomer.setFixedLine(row.getCell(7).getStringCellValue());
            ztCustomer.setName(row.getCell(8).getStringCellValue());
            ztCustomer.setEmail(row.getCell(9).getStringCellValue());
            ztCustomer.setPhone(row.getCell(10).getStringCellValue());
            ztCustomer.setReserved1(row.getCell(11).getStringCellValue());
            ztCustomer.setCreateTime(new Date());

            int sex = 1;
//            if ("女".equals(row.getCell(12).getStringCellValue())) {
//                sex = 0;
//            }

            if ("男".equals(row.getCell(12).getStringCellValue())) {
                sex = 0;
            }
            ztCustomer.setSex(sex);
            ztCustomer.setCreateTime(new Date());

            ztCustomerService.insertSelective(ztCustomer);
        }


    }


    /**
     * @return void
     * @Description //导出客户模板
     * @Param No such property: code for class: Script1
     * @Date 2024/3/11 10:10
     * @Author cx
     **/
    public void exportCustomsTemplate(String url) throws Exception {

        try {
            //制作Excel表格
            Workbook workbook = new XSSFWorkbook();
            //创建Excel表格
            Sheet sheet = workbook.createSheet("客户联系人");

            //在工作表中创建一行，行的索引是0
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue("客户名称");
            row.createCell(1).setCellValue("客户简称");
            row.createCell(2).setCellValue("国家/地区");
            row.createCell(3).setCellValue("企业网站");
            row.createCell(4).setCellValue("主营产品");
            row.createCell(5).setCellValue("客户类型");
            row.createCell(6).setCellValue("备注");
            row.createCell(7).setCellValue("固定电话");
            row.createCell(8).setCellValue("姓名");
            row.createCell(9).setCellValue("邮箱");
            row.createCell(10).setCellValue("手机");
            row.createCell(11).setCellValue("电话");
            row.createCell(12).setCellValue("性别");


            FileOutputStream fileOut = new FileOutputStream(url);

            workbook.write(fileOut);
            fileOut.close();

            // 关闭工作簿
            workbook.close();

        } catch (Exception e) {
            System.out.println("导出客户模板错误" + e);
        }


    }


    /**
     * @return void
     * @Description //导出客户列表
     * @Param No such property: code for class: Script1
     * @Date 2024/3/11 16:36
     * @Author cx
     **/
    public String exportCustoms(List<ZtCustomer> list) throws Exception {

        String excelUrl = url + new Date().getTime() + UtilsFields.EXPORT_TABLE_EXCEL;
        exportCustomsTemplate(excelUrl);
        FileInputStream inputStream = new FileInputStream(new File(excelUrl));
        //找到工作表而不是重新创建

        Workbook workbook = WorkbookFactory.create(inputStream);
        //获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);


        for (int i = 0; i < list.size(); i++) {
            Row rows = sheet.createRow(i + 1);
            rows.createCell(0).setCellValue(list.get(i).getCustomerName());
            rows.createCell(1).setCellValue(list.get(i).getCustomerAbbreviation());
            rows.createCell(2).setCellValue(list.get(i).getCountriseRegions());
            rows.createCell(3).setCellValue(list.get(i).getEnterpriseWebsite());
            rows.createCell(4).setCellValue(list.get(i).getMeritoProduct());
            rows.createCell(5).setCellValue(list.get(i).getCustomeType());
            rows.createCell(6).setCellValue(list.get(i).getRemark());
            rows.createCell(7).setCellValue(list.get(i).getFixedLine());
            rows.createCell(8).setCellValue(list.get(i).getName());
            rows.createCell(9).setCellValue(list.get(i).getEmail());
            rows.createCell(10).setCellValue(list.get(i).getPhone());
            rows.createCell(11).setCellValue(list.get(i).getReserved1());
            if (list.get(i).getSex() == 0) {
                rows.createCell(12).setCellValue("男");

            }
            if (list.get(i).getSex() == 1) {
                rows.createCell(12).setCellValue("女");
            }
            if (list.get(i).getSex() == 2) {
                rows.createCell(12).setCellValue("其它");

            }


        }

        // 将更改写回文件
        try (FileOutputStream out = new FileOutputStream(new File(excelUrl))) {
            workbook.write(out);
        } finally {
            // 确保在写回更改之后关闭Workbook
            if (workbook != null) {
                workbook.close();
            }
        }
        return  excelUrl;

    }


    public static void main(String[] args) throws Exception {
               makeExcel();    }
}
